package com.itheima.demo;

import com.itheima.domain.Student;
import com.itheima.utils.JDBCDruidUtils;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * JDBC的方式连接数据库
 */
public class JDBCDemo_01 {
    @Test
    public void selectAll() {
        //提升作用域好关流
                Connection connection =null;
                PreparedStatement preparedStatement =null;
                ResultSet resultSet =null;
        try {
            //这是利用反射技术，加载该类的字节码文件从而创建该类的对象达到注册驱动的目的。。。。。吧
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
             connection = DriverManager.getConnection("jdbc:mysql://47.101.183.148:3306/db1", "root", "itheima29");
            //编写SQL语句,查询全表数据
            String sql = "select * from Student";
            //获取预编译对象，参数传递SQL语句
             preparedStatement = connection.prepareStatement(sql);
            //执行，获取结果集对象
             resultSet = preparedStatement.executeQuery();
            //创建一个集合，准备接受查询出来的所有记录封装成的对象
            List<Student> list = new ArrayList<Student>();
            //死循环，直到游标下再无数据则说明全部遍历完成
            while (resultSet.next()){
                //按字段名取值
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                String age = resultSet.getString("age");
                //创建学生对象，封装信息
                Student student = new Student(Integer.parseInt(id),name,Integer.parseInt(age));
                //将学生对象添加进集合
                list.add(student);
            }
            //遍历集合，将每个学生对象输出
            for (Student student : list) {
                System.out.println("student = " + student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关流大法
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    @Test
    public void selectById() {
        //提升作用域好关流
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
            //根据id查找，还是先注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
             connection = DriverManager.getConnection("jdbc:mysql://47.101.183.148:3306/db1", "root", "itheima29");
            //编写SQL
            String sql = "select * from Student where id =?";
            //获取预编译对象，并给占位符赋值
             preparedStatement = connection.prepareStatement(sql);
             preparedStatement.setInt(1,2);
            //执行，获取结果集
             resultSet = preparedStatement.executeQuery();
            //处理结果，因为是单个查询，直接if，封装学生对象
            Student student =null;
            if(resultSet.next()){
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                String age = resultSet.getString("age");
                 student = new Student(Integer.parseInt(id), name, Integer.parseInt(age));
            }
            //打印对象即查询结果
            System.out.println("student = " + student);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //在此使用工具类关流
            JDBCDruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
    @Test
    public void selectInsert() {
        //提升作用域好关流
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
            //根据id查找，还是先注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
            connection = DriverManager.getConnection("jdbc:mysql://47.101.183.148:3306/db1", "root", "itheima29");
            //编写SQL
            String sql = "insert into Student values (?,?,?) ";
            //获取预编译对象，并给占位符赋值
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,null);
            preparedStatement.setString(2,"秃大炮");
            preparedStatement.setInt(3,6);
            //执行
            int i = preparedStatement.executeUpdate();
            //处理结果
            if(i>0){
                System.out.println("添加成功！");
            }else {
                System.out.println("添加失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //在此使用工具类关流
            JDBCDruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
    @Test
    public void selectUpdate() {
        //提升作用域好关流
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
            //根据id查找，还是先注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
            connection = DriverManager.getConnection("jdbc:mysql://47.101.183.148:3306/db1", "root", "itheima29");
            //编写SQL
            String sql = "update Student set name=? where id=? ";
            //获取预编译对象，并给占位符赋值
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"秃巨炮");
            preparedStatement.setInt(2,12);
            //执行
            int i = preparedStatement.executeUpdate();
            //处理结果
            if(i>0){
                System.out.println("修改成功！");
            }else {
                System.out.println("修改失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //在此使用工具类关流
            JDBCDruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
    @Test
    public void selectDelete() {
        //提升作用域好关流
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        try {
            //根据id查找，还是先注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
            connection = DriverManager.getConnection("jdbc:mysql://47.101.183.148:3306/db1", "root", "itheima29");
            //编写SQL
            String sql = "delete from Student  where id=? ";
            //获取预编译对象，并给占位符赋值
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,12);
            //执行
            int i = preparedStatement.executeUpdate();
            //处理结果
            if(i>0){
                System.out.println("删除成功！");
            }else {
                System.out.println("删除失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //在此使用工具类关流
            JDBCDruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
}
